package com.lagou.dao.impl;

import com.lagou.dao.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class CourseContentDaoImpl implements CourseContentDao {
    /**
     * 根据课程ID查询课程相关信息
     */

    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写SQL
            String sql ="SELECT \n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num`course_section`\n" +
                    "FROM course_section WHERE course_id = ?";
            //3.执行查询
            List<Course_Section> sectionList = qr.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);

            //4.根据章节ID查询课时信息
            for (Course_Section section : sectionList) {
                //调用方法 获取章节对应的课时
                List<Course_Lesson> lessonList = findLessonBySectionId(section.getId());
                //将课时数据封装到 章节对象中
                section.setLessonList(lessonList);
            }
            return sectionList;



        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }
    /**
     * 根据章节id查询 课时信息
     */
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql ="SELECT\n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num\n" +
                    "FROM course_lesson WHERE section_id =?";
            List<Course_Lesson> lessonList = qr.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);

            return lessonList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }


    }

    /**
     * 添加章节时 ，回显数据（回显课程名称 ）
     * @param courseId
     * @return
     */
    @Override
    public Course findCourseByCourseId(int courseId) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写SQL
            String sql ="SELECT id,course_name FROM course WHERE id = ?";
            //3.执行查询
            Course course = qr.query(sql, new BeanHandler<Course>(Course.class), courseId);
            //4、返回结果
            return course;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 保存章节信息
     */
    @Override
    public int saveSection(Course_Section section) {

        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL
            String sql = "INSERT INTO course_section\n" +
                    "(course_id,section_name,description,order_num,STATUS,create_time,update_time)\n" +
                    "VALUES\n" +
                    "(?,?,?,?,?,?,?)";
            //3.准备参数
            Object[] param ={section.getCourse_id(),section.getSection_name(),section.getDescription(),
                    section.getOrder_num(),section.getStatus(),section.getCreate_time(),section.getUpdate_time()};
            //4.执行插入
//            int i = qr.update(sql,section.getCourse_id(),param);
            int i = qr.update(sql,param);
            //4.返回结果
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }

    /**
     * 修改章节信息
     * @param section
     * @return
     */
    @Override
    public int updateSection(Course_Section section) {
        try {
            //1、创建qr
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2、编写sql
            String sql ="UPDATE course_section SET\n" +
                    "section_name = ?,\n" +
                    "description =?,\n" +
                    "update_time=?,\n" +
                    "order_num=?\n" +
                    "WHERE id = ?;";
            //3、传值
            Object [] param ={
                    section.getSection_name(),section.getDescription(),section.getUpdate_time(),section.getOrder_num()
                    ,section.getId() };


            //4、执行并返回结果
            int row = qr.update(sql, param);
            return row;

        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }

    /**
     * 修改章节状态
     * */

    @Override
    public int updateSectionStatus(int id, int Status) {

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            String sql = "UPDATE course_section SET STATUS = ?,update_time = ? WHERE id = ?;";

            Object[] param = {Status, DateUtils.getDateFormart(), id};
            //4.执行修改操作
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }




    /**   作业 -----------------------------------------------------------------
     * 作业代码   第一步  回显课程id  回显章节id   分别查出来两个回显信息
     *              第二步  新建（添加）课时操作
     *              第三步  修改（update）课时内容
     *
     * 添加课时时 ，回显数据（回显章节名称 ）
     *
     *
     */
    @Override
    public Course_Section findSectionBySectionId(int sectionId) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //创建sql查询
            String sql = "SELECT id,course_id,section_name\n" +
                    "FROM course_section WHERE id =1;";
            //3.执行查询
            Course_Section section = qr.query(sql, new BeanHandler<Course_Section>(Course_Section.class));
            //4、返回查询结果
            return section;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 新建课时信息功能
     * @param lesson
     * @return
     */
    @Override
    public int saveLesson(Course_Lesson lesson) {

        try {
            //1 、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql  （新增）
            String sql= "INSERT INTO course_lesson\n" +
                    "(course_id,section_id,theme,duration,is_free,order_num)\n" +
                    "VALUE(?,?,?,?,?,?);";

            //3.准备参数

            Object[] param ={
                    lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),
                    lesson.getDuration(),lesson.getIs_free(),lesson.getOrderNum()};

            //4.执行插入
            int row = qr.update(sql, param);
            //5、返回结果
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }
    /**
     * 修改课时信息
     * */
    @Override
    public int updateLesson(Course_Lesson lesson) {

        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL

            String sql ="UPDATE course_lesson \n" +
                    "SET course_id =?,section_id=?,theme=?,duration=?,is_free=?,order_num=?\n" +
                    "WHERE id=?;";

            //3.准备参数
            Object[] param = {lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),
                    lesson.getDuration(), lesson.getIs_free(),lesson.getOrderNum()};
            //4.执行修改操作
            int row = qr.update(sql, param);

            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }


    }


}
